package com.naiterui.ehp.bs.agent.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.agent.entity.AsAreaCount;

/**
 * 经纪人数据统计-区域数据统计表
 *
 * @author yangfuya
 * @since 2022/2/22
 */
@Repository
public interface IAsAreaCountRepository extends BaseJpaRepository<AsAreaCount, Long> {

    @Query(value = "select *  from as_area_count where generate_date =:generateDate  limit 1", nativeQuery = true)
    AsAreaCount getTotalByGenerateDate(@Param("generateDate") String generateDate);

    @Query(value = "SELECT * FROM as_area_count WHERE TO_DAYS(:yesterday) - TO_DAYS(generate_date) = 1",
        nativeQuery = true)
    List<AsAreaCount> getYesterday(@Param("yesterday") String yesterday);

    @Query(value = "select sum(agent_total) agent_total,sum(recipel_total) recipel_total, sum(order_price_total) order_price_total, sum(new_number) new_number from as_area_count " +
            "where provice_area_id in (:proviceAreaIds) and generate_date > :startTime and generate_date < :endTime",
            nativeQuery = true)
    List<Object[]> getSummaryByProviceAreaId(@Param("proviceAreaIds") List<Long> proviceAreaIds, @Param("startTime") String startTime, @Param("endTime") String endTime);
}
